﻿
////////////////////////////////////////////////////////////////////////////////////////////
// 文件名: ETL_Source_ViewConfigDAL.gen.cs
//
// 模块名称: ETLSourceView.Dal
//
// 作者: LPC
//
// 历史记录（记录修改记录，修改文件后请添加修改记录，注明修改时间、修改人，修改内容）:
// 13-2-5    LPC    创建文件
////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using Common;
using Model;
using System.Data.SqlTypes;

namespace DAL
{
    /// <summary>
    /// ETL_Source_ViewConfig表数据访问层
    /// </summary>
    public class EtlSourceViewConfigDAL : DB
    {
        public EtlSourceViewConfigDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }
        /// <summary>
        /// 根据GUID字段获取ETL_Source_ViewConfigInfo实体对象
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <returns>ETL_Source_ViewConfigInfo实体对象</returns>
        public ETL_Source_ViewConfigInfo GetInfoById(string guid)
        {
            var list = new List<ETL_Source_ViewConfigInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            IDataParameter[] sqlparams = { 
             new SqlParameter("@GUID",guid)    
            };

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.GetDataTable("select [GUID],[SourceSystemID],[DBID],[ViewName],[DisplayName],[ViewChnName],[MDP_ViewSchema],[MDP_ViewName],[CreateTableScript],[PagingNum],[TableMode],[FileFormat],[TextQualifier],[LineSeparator],[ColumnSeparator],[SkipRowNum],[SkipDataNum] FROM [Working_db].ETL_Source_ViewConfig where GUID=@GUID", sqlparams);
                list = dt.MapToList<ETL_Source_ViewConfigInfo>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return null;
        }

        /// <summary>
        /// 往ETL_Source_ViewConfig表内添加新记录
        /// </summary>
        /// <param name="sqlparams"> </param>
        /// <returns>添加记录索引值</returns>
        public int Insert(IDataParameter[] sqlparams)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();


            //执行命令返回结果
            try
            {
                cmd.StoredUpdate("Working_db.UP_ETL_Source_ViewConfig_Insert", sqlparams);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_ViewConfigInfo_Insert", "UP_Sys_ETL_Source_ViewConfigInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        /// <summary>
        /// 更新ETL_Source_ViewConfig表记录
        /// </summary>
        public int Update(ETL_Source_ViewConfigInfo eTLSourceViewConfigInfo)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            IDataParameter[] sqlparams = {
            new SqlParameter("@GUID",eTLSourceViewConfigInfo.Guid),
            new SqlParameter("@SourceSystemID",eTLSourceViewConfigInfo.SourceSystemID),
            new SqlParameter("@ViewName",eTLSourceViewConfigInfo.ViewName),
            new SqlParameter("@ViewChnName",eTLSourceViewConfigInfo.ViewChnName),
            new SqlParameter("@CreateTableScript",eTLSourceViewConfigInfo.CreateTableScript),
            new SqlParameter("@CreateUser",eTLSourceViewConfigInfo.CreateUser),
            new SqlParameter("@CreateTime",eTLSourceViewConfigInfo.CreateTime),
            new SqlParameter("@UpdateUser",eTLSourceViewConfigInfo.UpdateUser),
            new SqlParameter("@UpdateTime",eTLSourceViewConfigInfo.UpdateTime),
            new SqlParameter("@DeleteUser",eTLSourceViewConfigInfo.DeleteUser),
            new SqlParameter("@DeleteTime",eTLSourceViewConfigInfo.DeleteTime),
            new SqlParameter("@IsDelete",eTLSourceViewConfigInfo.IsDelete)
                                         };

            //执行命令返回结果
            try
            {
                cmd.StoredUpdate("Working_db.UP_ETL_Source_ViewConfig_Update", sqlparams);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_ViewConfigInfo_Insert", "UP_Sys_ETL_Source_ViewConfigInfo_Insert", 0, 1, ex, "", "");
            }

            return num;
        }

        public int AutoConfigModel(string viewName)
        {
            int num = 1;

            //创建Database对象          
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@Staging_dbTableName", viewName) };
                cmd.StoredUpdate("[working_db].[UP_AutoConfigModel]", sqlparams);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_AutoConfigModel", "UP_AutoConfigModel", 0, 1, ex, "", "");
                num = -1;
            }
            return num;
        }
        /// <summary>
        /// 根据GUID字段删除ETL_Source_ViewConfigInfo表信息
        /// </summary>
        /// <param name="guid">GUID</param>
        public int DeleteView(SqlParameter[] guid)
        {
            int num = -1;

            //创建Database对象          
            HIDB.dbo cmd = base.conn();


            try
            {
                cmd.StoredUpdate("working_db.[UP_ETL_Source_ViewConfig_Delete]", guid);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_RoleInfo_Insert", "UP_Sys_RoleInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }
        public List<BigDataSouceViewInit> GetAllInit(string viewID)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] para = new SqlParameter[] { 
                new SqlParameter("@ViewID",viewID)
                };
                DataTable dt = cmd.GetDataTable(@"select * from [Working_db].[BigDataSouceViewInit] where ViewID=@ViewID order by SortNum", para);
                return dt.MapToList<BigDataSouceViewInit>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public IncrementRole GetIncrementRole(string viewID)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                IList<IncrementRole> list = new List<IncrementRole>();
                SqlParameter[] para = new SqlParameter[] { 
                new SqlParameter("@ViewID",viewID)
                };
                DataTable dt = cmd.GetDataTable(@"select * from [Working_db].[IncrementRole] where ViewID=@ViewID", para);
                list = dt.MapToList<IncrementRole>();
                if (list.Count > 0)
                    return list[0];
                else
                    return new IncrementRole();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int AddInitBigData(SqlParameter[] para)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                cmd.ExecuteSql(@"insert into [Working_db].[BigDataSouceViewInit]([GUID],[ViewID],[ColumnValue],[SortNum],[CreateTime]) 
values(newid(),@ViewID,@ColumnValue,@SortNum,getdate())", para);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int DeleteInitBigData(string viewID)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] para = new SqlParameter[] { 
                        new SqlParameter("@ViewID",viewID) 
                        };
                cmd.ExecuteSql(@"delete from [Working_db].[BigDataSouceViewInit] where ViewID=@ViewID", para);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 根据实体获取ETL_Source_ViewConfigInfo实体List对象
        /// </summary>
        /// <returns>ETL_Source_ViewConfigInfo实体对象</returns>
        public List<ETL_Source_ViewConfigInfo> GetList(IDataParameter[] par)
        {
            var list = new List<ETL_Source_ViewConfigInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            //IDataParameter[] sqlparams = {
            //new SqlParameter("@GUID",eTLSourceViewConfigInfo.Guid),
            //new SqlParameter("@SourceSystemID",eTLSourceViewConfigInfo.SourceSystemID),
            //new SqlParameter("@ViewName",eTLSourceViewConfigInfo.ViewName),
            //new SqlParameter("@ViewChnName",eTLSourceViewConfigInfo.ViewChnName),
            //new SqlParameter("@CreateTableScript",eTLSourceViewConfigInfo.CreateTableScript),
            //new SqlParameter("@CreateUser",eTLSourceViewConfigInfo.CreateUser),
            //new SqlParameter("@CreateTime",eTLSourceViewConfigInfo.CreateTime),
            //new SqlParameter("@UpdateUser",eTLSourceViewConfigInfo.UpdateUser),
            //new SqlParameter("@UpdateTime",eTLSourceViewConfigInfo.UpdateTime),
            //new SqlParameter("@DeleteUser",eTLSourceViewConfigInfo.DeleteUser),
            //new SqlParameter("@DeleteTime",eTLSourceViewConfigInfo.DeleteTime),
            //new SqlParameter("@IsDelete",eTLSourceViewConfigInfo.IsDelete),
            //};

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("", par);
                list = CreateEtlSourceViewConfigInfo(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_ViewConfigInfo_Select", "UP_Sys_ETL_Source_ViewConfigInfo_Select", 0, 1, ex, "", "");
            }
            if (list != null)
            {
                return list;
            }
            return null;
        }

        /// <summary>
        /// 根据实体获取ETL_Source_ViewConfigInfo实体List对象
        /// </summary>
        /// <returns>ETL_Source_ViewConfigInfo实体对象</returns>
        public int GetListCount(IDataParameter[] par)
        {
            int rowCount = 0;
            new List<ETL_Source_ViewConfigInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数


            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("", par);
                rowCount = dt.Rows.Count;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_ViewConfigInfo_Select", "UP_Sys_ETL_Source_ViewConfigInfo_Select", 0, 1, ex, "", "");
            }
            return rowCount;
        }


        public string GetSqlTypeName(string typeName, string connType)
        {
            string sql = "";
            switch (connType)
            {
                case "2":
                    sql = string.Format(@"SELECT TOP 1 [SqlServer_Type] FROM [Working_db].[GetAllTypes] where [oracle_Type]='{0}'", typeName);
                    break;
                case "4":
                    sql = string.Format(@"SELECT TOP 1 [SqlServer_Type] FROM [Working_db].[GetAllTypes_SqlToMySQL] where [mysql_Type]='{0}'", typeName);
                    break;
                case "8":
                    sql = string.Format(@"SELECT TOP 1 [SqlServer_Type] FROM [Working_db].[GetAllTypes_SqlToNpgSQL] where [Npgsql_Type]='{0}'", typeName);
                    break;
            }
            HIDB.dbo cmd = base.conn();
            object obj = cmd.GetScalar(sql);
            if (obj != null && !(obj.ToString().Equals("")))
                return obj.ToString();
            return typeName;
        }
        /// <summary>
        /// 生成ETL_Source_ViewConfigInfo实体对象
        /// </summary>
        private List<ETL_Source_ViewConfigInfo> CreateEtlSourceViewConfigInfo(DataTable dt)
        {
            var list = new List<ETL_Source_ViewConfigInfo>();
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                var myEtlSourceViewConfigInfo = new ETL_Source_ViewConfigInfo
                {
                    Guid = DBConvert.GetString(dt.Rows[j], "GUID"),
                    SourceSystemID = DBConvert.GetString(dt.Rows[j], "SourceSystemID"),
                    ViewName = DBConvert.GetString(dt.Rows[j], "ViewName"),
                    ViewChnName = DBConvert.GetString(dt.Rows[j], "ViewChnName"),
                    CreateTableScript = DBConvert.GetString(dt.Rows[j], "CreateTableScript"),
                    CreateUser = DBConvert.GetString(dt.Rows[j], "CreateUser"),
                    CreateTime = DBConvert.GetDateTime(dt.Rows[j], "CreateTime"),
                    UpdateUser = DBConvert.GetString(dt.Rows[j], "UpdateUser"),
                    UpdateTime = DBConvert.GetDateTime(dt.Rows[j], "UpdateTime"),
                    DeleteUser = DBConvert.GetString(dt.Rows[j], "DeleteUser"),
                    DeleteTime = DBConvert.GetDateTime(dt.Rows[j], "DeleteTime"),
                    IsDelete = DBConvert.GetInt32(dt.Rows[j], "IsDelete")
                };
                list.Add(myEtlSourceViewConfigInfo);
            }
            return list;
        }

        public int CreateStgTB(IDataParameter[] paras)
        {
            int result = 1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                result = int.Parse(cmd.StoredDataTable("[Working_db].[CreateStgTBfromDataSource]", paras).Rows[0][0].ToString());
                if (result != 1)
                {
                    result = -1;
                }
            }
            catch (Exception ex)
            {
                Logger.LogError("CreateStgTBfromDataSource", "CreateStgTBfromDataSource", 0, 1, ex, "", "");
                result = -1;
            }
            return result;
        }

        public void RollBack(string id)
        {
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@GUID", id) };
                string sql = "delete [Working_db].[ETL_Source_ViewConfig] where [GUID]=@GUID";
                cmd.ExecuteSql(sql, sqlparams);

                sql = "delete [Working_db].[ETL_Source_View_ColumnConfig] where [ViewID]=@GUID";
                cmd.ExecuteSql(sql, sqlparams);
            }
            catch (Exception ex)
            {
                Logger.LogError("CreateStgTBfromDataSource", "CreateStgTBfromDataSource", 0, 1, ex, "", "");
            }
        }
        public List<ETL_Source_ViewConfigInfo> GetViewById(SqlParameter[] par)
        {
            var list = new List<ETL_Source_ViewConfigInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();


            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("Working_db.UP_ETL_Source_ViewConfig_Select", par);
                list = CreateEtlSourceViewConfigInfo(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_ViewConfigInfo_Select", "UP_Sys_ETL_Source_ViewConfigInfo_Select", 0, 1, ex, "", "");
            }
            if (list != null)
            {
                return list;
            }
            return null;
        }


        #region ETL_Source_ViewConfig

        /// <summary>
        /// 根据GUID字段获取ETL_Source_ViewConfigInfo实体对象
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <returns>ETL_Source_ViewConfigInfo实体对象</returns>
        public ETL_Source_ViewConfigInfo GetSourceViewConfigInfoByGuid(string guid)
        {
            IList<ETL_Source_ViewConfigInfo> list = new List<ETL_Source_ViewConfigInfo>();
            try
            {
                //创建DbCommand对象
                HIDB.dbo cmd = base.conn();

                //添加输入输出参数
                IDataParameter[] cmdParms = { new SqlParameter("@GUID", guid) };
                string strSql = @"SELECT * FROM [Working_db].ETL_Source_ViewConfig WHERE GUID=@GUID";
                DataTable dts = cmd.GetDataTable(strSql, cmdParms);
                list = dts.MapToList<ETL_Source_ViewConfigInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("GetSourceViewConfigInfoById", "GetSourceViewConfigInfoById", 0, 1, ex, "", "");
            }
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return null;
        }

        public ETL_Source_ViewConfigInfo GetSourceViewConfigInfoByViewName(string id, string viewName)
        {
            IList<ETL_Source_ViewConfigInfo> list = new List<ETL_Source_ViewConfigInfo>();
            try
            {
                //创建DbCommand对象
                HIDB.dbo cmd = base.conn();

                //添加输入输出参数
                IDataParameter[] cmdParms = { 
                                                new SqlParameter("@SourceSystemID", id),
                                                new SqlParameter("@ViewName", viewName),
                                            };
                string strSql = @"SELECT * FROM [Working_db].ETL_Source_ViewConfig 
WHERE SourceSystemID=@SourceSystemID AND ViewName=@ViewName";
                DataTable dts = cmd.GetDataTable(strSql, cmdParms);
                list = dts.MapToList<ETL_Source_ViewConfigInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("GetSourceViewConfigInfoById", "GetSourceViewConfigInfoById", 0, 1, ex, "", "");
            }
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return null;
        }
        public ETL_Task_List_History GetStepStatus(string id)
        {
            try
            {
                HIDB.dbo cmd = base.conn();

                IDataParameter[] cmdParms = { 
                new SqlParameter("@JobStepID", id) 
                };
                string strSql = @"select top 1 * from [Working_db].[ETL_Task_List_History] where JobStepID like @JobStepID+'%' order by BatchTime desc";
                DataTable dts = cmd.GetDataTable(strSql, cmdParms);
                List<ETL_Task_List_History> List = dts.MapToList<ETL_Task_List_History>();
                List = dts.MapToList<ETL_Task_List_History>();
                if (List.Count > 0)
                    return List[0];
                else
                    return new ETL_Task_List_History() { TaskStatus = 1 };
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public int DeleteSourceViewConfig(string guid)
        {
            int num = -1;

            //创建Database对象          
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = "DELETE FROM [Working_db].[ETL_Source_ViewConfig] WHERE GUID=@GUID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",guid)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_RoleInfo_Insert", "UP_Sys_RoleInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        /// <summary>
        /// 往ETL_Source_ViewConfig表内添加新记录
        /// </summary>
        /// <param name="sqlparams"> </param>
        /// <returns>添加记录索引值</returns>
        public int InsertSourceViewConfig(ETL_Source_ViewConfigInfo sourceViewConfigInfo)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"INSERT INTO [Working_db].[ETL_Source_ViewConfig]  (GUID,SourceSystemID,DBID,WildCardID,ViewName,ViewChnName,DisplayName,TableMode,CreateTableScript,CreateUser,CreateTime
,MDP_ViewName,FileFormat,TextQualifier,LineSeparator,ColumnSeparator,SkipRowNum,SkipDataNum) VALUES(@GUID,@SourceSystemID,@DBID,@WildCardID,@ViewName,@ViewChnName,@DisplayName,@TableMode,@CreateTableScript,@CreateUser,@CreateTime
,@MDP_ViewName,@FileFormat,@TextQualifier,@LineSeparator,@ColumnSeparator,@SkipRowNum,@SkipDataNum)";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",sourceViewConfigInfo.Guid)
                     ,new SqlParameter("@SourceSystemID",sourceViewConfigInfo.SourceSystemID)
                     ,new SqlParameter("@DBID",sourceViewConfigInfo.DBID)
                     ,new SqlParameter("@WildCardID",sourceViewConfigInfo.WildCardID)
                     ,new SqlParameter("@ViewName",sourceViewConfigInfo.ViewName)
                     ,new SqlParameter("@ViewChnName",sourceViewConfigInfo.ViewChnName??string.Empty)
                     ,new SqlParameter("@DisplayName",sourceViewConfigInfo.DisplayName??string.Empty)
                     ,new SqlParameter("@TableMode",sourceViewConfigInfo.TableMode)
                     ,new SqlParameter("@CreateTableScript",sourceViewConfigInfo.CreateTableScript??string.Empty)
                     ,new SqlParameter("@CreateUser",sourceViewConfigInfo.CreateUser??string.Empty)
                     ,new SqlParameter("@CreateTime",sourceViewConfigInfo.CreateTime)
                     ,new SqlParameter("@MDP_ViewName",sourceViewConfigInfo.MDP_ViewName)
                     ,new SqlParameter("@FileFormat",sourceViewConfigInfo.FileFormat??string.Empty)
                     ,new SqlParameter("@TextQualifier",sourceViewConfigInfo.TextQualifier??string.Empty)
                     ,new SqlParameter("@LineSeparator",sourceViewConfigInfo.LineSeparator??string.Empty)
                     ,new SqlParameter("@ColumnSeparator",sourceViewConfigInfo.ColumnSeparator??string.Empty)
                     ,new SqlParameter("@SkipRowNum",sourceViewConfigInfo.SkipRowNum)
                     ,new SqlParameter("@SkipDataNum",sourceViewConfigInfo.SkipDataNum)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_ViewConfigInfo_Insert", "UP_Sys_ETL_Source_ViewConfigInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }
        public int AddBigDateTagConfig(string viewID, string sysID)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"insert into [Working_db].[BigDateTagConfig]
select NEWID(),@GUID,GUID,'',GETDATE(),'','' from Working_db.ETL_DbConfig where SysID=@sysID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",viewID)
                     ,new SqlParameter("@sysID",sysID)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_ViewConfigInfo_Insert", "UP_Sys_ETL_Source_ViewConfigInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }
        public int UpdateSourceViewConfig(ETL_Source_ViewConfigInfo sourceViewConfigInfo)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"UPDATE [Working_db].[ETL_Source_ViewConfig]  
SET SourceSystemID=@SourceSystemID,ViewName=@ViewName,ViewChnName=@ViewChnName,DisplayName=@DisplayName
,TableMode=@TableMode,BigDataTag=@BigDataTag,IsDistinct=@IsDistinct,CreateTableScript=@CreateTableScript
,FileFormat=@FileFormat,TextQualifier=@TextQualifier,LineSeparator=@LineSeparator
,ColumnSeparator=@ColumnSeparator,SkipRowNum=@SkipRowNum,SkipDataNum=@SkipDataNum
where GUID=@GUID;
delete from [Working_db].[BigDateTagConfig] where ViewID=@GUID;
insert into [Working_db].[BigDateTagConfig]
select NEWID(),@GUID,GUID,'',GETDATE(),'','' from Working_db.ETL_DbConfig where SysID=@SourceSystemID
";
                // ,DeleteUser=@DeleteUser,DeleteTime=@DeleteTime,IsDelete=@IsDelete 
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",sourceViewConfigInfo.Guid)
                     ,new SqlParameter("@SourceSystemID",sourceViewConfigInfo.SourceSystemID)
                     ,new SqlParameter("@ViewName",sourceViewConfigInfo.ViewName)
                     ,new SqlParameter("@ViewChnName",sourceViewConfigInfo.ViewChnName??string.Empty)
                     ,new SqlParameter("@DisplayName",sourceViewConfigInfo.DisplayName??string.Empty)
                     ,new SqlParameter("@TableMode",sourceViewConfigInfo.TableMode)  
                     ,new SqlParameter("@BigDataTag",sourceViewConfigInfo.BigDataTag)  
                     ,new SqlParameter("@IsDistinct",sourceViewConfigInfo.IsDistinct)  
                     ,new SqlParameter("@CreateTableScript",sourceViewConfigInfo.CreateTableScript)  
                     ,new SqlParameter("@FileFormat",sourceViewConfigInfo.FileFormat??string.Empty)
                     ,new SqlParameter("@TextQualifier",sourceViewConfigInfo.TextQualifier??string.Empty)
                     ,new SqlParameter("@LineSeparator",sourceViewConfigInfo.LineSeparator??string.Empty)
                     ,new SqlParameter("@ColumnSeparator",sourceViewConfigInfo.ColumnSeparator??string.Empty)
                     ,new SqlParameter("@SkipRowNum",sourceViewConfigInfo.SkipRowNum)
                     ,new SqlParameter("@SkipDataNum",sourceViewConfigInfo.SkipDataNum)
                };

                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UpdateSourceViewConfig", "UpdateSourceViewConfig", 0, 1, ex, "", "");
            }
            return num;
        }

        public int UpdateIncrementRole(IncrementRole irole)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"delete from [Working_db].[IncrementRole] where ViewID=@ViewID;
insert into [Working_db].[IncrementRole](GUID,ViewID,RoleType,DateType,IncrementNum,CreateTime)
values(newid(),@ViewID,@RoleType,@DateType,@IncrementNum,getdate())";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@ViewID",irole.ViewID)
                     ,new SqlParameter("@RoleType",irole.RoleType)
                     ,new SqlParameter("@DateType",irole.DateType)
                     ,new SqlParameter("@IncrementNum",irole.IncrementNum) 
                };

                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }
        public List<ETL_Source_ViewConfigInfo> GetViewConfigById(string sourceSystemID, string sort, string order, int pageIndex, int pageSize, out int recordCount)
        {
            recordCount = 0;

            List<ETL_Source_ViewConfigInfo> result = new List<ETL_Source_ViewConfigInfo>();
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = string.Format(@"SELECT v.GUID,SourceSystemID,ViewName,ViewChnName,DisplayName,TableMode,CreateTableScript,0 as StepStatus,v.CreateTime
FROM [Working_db].ETL_Source_ViewConfig v WHERE v.SourceSystemID=@SourceSystemID order by {0} {1}", sort, order);
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@SourceSystemID",sourceSystemID)
                };
                DataTable dt = cmd.GetDataTable(strSql, cmdParms);
                result = dt.MapToList<ETL_Source_ViewConfigInfo>();
                recordCount = result.Count;

                if (pageIndex > -1 && pageSize > -1)
                {
                    result = result.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                }
            }
            catch (Exception ex)
            {
                Logger.LogError("GetViewConfigById", "GetViewConfigById", 0, 1, ex, "", "");
            }
            return result;
        }

        /// <summary>
        /// 删除配置的源系统的表信息及其对应的字段信息
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public int DeleteSourceViewAndColumnConfig(string guid)
        {
            int num = -1;

            //创建Database对象          
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"DELETE [Working_db].[ETL_Source_View_ColumnConfig] where [ViewID]=@GUID;
                DELETE FROM [Working_db].[ETL_Source_ViewConfig] WHERE GUID=@GUID;
delete from [Working_db].[BigDateTagConfig] where ViewID=@GUID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",guid)
                };
                num = cmd.ExecuteSql(strSql, cmdParms); 

            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_RoleInfo_Insert", "UP_Sys_RoleInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        public List<ETL_Source_ViewConfigInfo> GetByDisplayNameWithOutGUID(string guid, string displayName)
        {
            List<ETL_Source_ViewConfigInfo> result = new List<ETL_Source_ViewConfigInfo>();
            try
            {
                string strSql = "SELECT * FROM [Working_db].[ETL_Source_ViewConfig] WHERE 1=1";
                List<IDataParameter> cmdParms = new List<IDataParameter>();

                strSql += " AND DisplayName =@DisplayName";
                cmdParms.Add(new SqlParameter("@DisplayName", displayName));

                strSql += " AND GUID!=@GUID";
                cmdParms.Add(new SqlParameter("@GUID", guid));

                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<ETL_Source_ViewConfigInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("GetByDisplayNameWithOutGUID", "GetByDisplayNameWithOutGUID", 0, 1, ex, "", "");
            }
            return result;

        }
        /// <summary>
        /// 是否存在相同的标识
        /// </summary>
        /// <param name="tag"></param>
        /// <returns></returns>
        public bool IsExistByBigDataTag(string viewID, string tag)
        {
            try
            {
                string strSql = "SELECT count(GUID) FROM [Working_db].[ETL_Source_ViewConfig] WHERE GUID!=@viewID and BigDataTag=@BigDataTag";
                List<IDataParameter> cmdParms = new List<IDataParameter>();
                cmdParms.Add(new SqlParameter("@BigDataTag", tag));
                cmdParms.Add(new SqlParameter("@viewID", viewID));

                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                if (dt.Rows[0][0].ToString() == "0")
                    return false;
                else
                    return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region 公共存储过程单元

        public int CreateStgTB(string viewID)
        {
            int result = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@View_ID_p",viewID)
                };
                DataTable dt = cmd.StoredDataTable("[Working_db].[CreateStgTBfromDataSource]", cmdParms);
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = int.Parse(dt.Rows[0][0].ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        #endregion

        #region 非存储过程单元

        public int RollBackSourceViewConfig(string viewID, bool deleteSourceViewConfig)
        {
            HIDB.dbo cmd = base.conn();
            int num = -1;
            //执行命令返回结果
            try
            {
                num = 0;
                SqlParameter[] cmdParms = new SqlParameter[] { 
                    new SqlParameter("@ViewID", viewID) 
                };
                string strSql = "DELETE [Working_db].[ETL_Source_View_ColumnConfig] WHERE [ViewID]=@ViewID";
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("CreateStgTBfromDataSource", "CreateStgTBfromDataSource", 0, 1, ex, "", "");
            }
            return num;
        }

        #endregion

    }
}